package cn.careerx.app.common.models;

import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.avaje.ebean.EbeanServer;
import com.avaje.ebean.SqlRow;
import com.mysql.jdbc.StringUtils;


/**
 * 问答model 
 * @author YuNingbo
 * 2015年8月21日
 */
public class LeftOrRight implements Serializable{

	private static final long serialVersionUID = -4217368983521302440L;
	
	private String id;
	private String ownerId;
	private String nickName;
	private String headImg;
	private String title;
	private String pics;
	private String options;
	private String content;
	private Integer commentCount;
	private String createAt;
	private Integer myResult;
	
	public static LeftOrRight create(){
		return new LeftOrRight();
	}
	
	public static ArrayList<LeftOrRight> getLorListByOwnerId(Database db,String owerId) throws Exception{
		EbeanServer es = db.getDbByPartitionKey("CAREERX", "");
		List<SqlRow> list = es.createSqlQuery("SELECT * FROM CX_LEFT_OR_RIGHT WHERE OWNER_ID=? ORDER BY CREATE_AT DESC")
		  .setParameter(1, owerId)
		  .findList();
		return convert(list);
	}
	
	public static ArrayList<LeftOrRight> getJoindListByUserId(Database db, String uId)  throws Exception{
		EbeanServer es = db.getDbByPartitionKey("CAREERX", "");
		List<SqlRow> list = es.createSqlQuery("SELECT LOR.* FROM CX_LEFT_OR_RIGHT LOR JOIN CX_LOR_RESULT R ON R.LOR_ID=LOR.ID WHERE R.USER_ID=? ORDER BY R.CREATE_AT DESC")
							  .setParameter(1, uId)
							  .findList();
		return convert(list);
	}
	
	public static ArrayList<LeftOrRight> getPageLorList(Database db, String uId,Integer page, Integer pageSize)  throws Exception{
		EbeanServer es = db.getDbByPartitionKey("CAREERX", "");
		List<SqlRow> list = es.createSqlQuery("SELECT LR.*,U.NICKNAME,U.PORTRAIT_KEY,CLR.RESULT FROM CX_LEFT_OR_RIGHT LR JOIN CX_USER U ON LR.OWNER_ID=U.ID LEFT JOIN CX_LOR_RESULT CLR ON CLR.LOR_ID=LR.ID WHERE U.ID=? ORDER BY CREATE_AT DESC")
				.setFirstRow((page-1)*pageSize+1).setMaxRows(pageSize)
				.setParameter(1, uId)
				.findList();
		return convert(list);
	}
	
	public static LeftOrRight getLeftOrRightById(Database db,String id) throws Exception{
		EbeanServer es = db.getDbByPartitionKey("CAREERX", "");
		SqlRow row = es.createSqlQuery("SELECT LR.*,U.NICKNAME,U.PORTRAIT_KEY,CLR.RESULT FROM CX_LEFT_OR_RIGHT LR JOIN CX_USER U ON LR.OWNER_ID=U.ID JOIN CX_LOR_RESULT CLR ON CLR.LOR_ID=LR.ID WHERE LR.ID=?")
		  .setParameter(1, id).findUnique();
		return convert(row);
	}
	
	public static int addLefOrRight(Database db,LeftOrRight lor) throws Exception{
		EbeanServer es = db.getDbByPartitionKey("CAREERX", "");
		StringBuffer sql = new StringBuffer(300);
		sql.append("INSERT INTO CX_LEFT_OR_RIGHT")
            .append("(ID,OWNER_ID,TITLE,PICS,OPTIONS,CONTENT,COMMENT_COUNT,CREATE_AT)")
            .append(" VALUES (?,?,?,?,?,?,?,now())");
		return es.createSqlUpdate(sql.toString())
			.setParameter(1, lor.getId())
			.setParameter(2, lor.getOwnerId())
			.setParameter(3, lor.getTitle())
			.setParameter(4, lor.getPics())
			.setParameter(5, lor.getOptions())
			.setParameter(6, lor.getContent())
			.setParameter(7, lor.getCommentCount()).execute();
	}
	
	public static int updateLeftOrRight(Database db,LeftOrRight lor) throws Exception{
		EbeanServer es = db.getDbByPartitionKey("CAREERX", "");
		StringBuffer sql = new StringBuffer(300);
		sql.append("UPDATE CX_LEFT_OR_RIGHT SET ");
		if(!StringUtils.isNullOrEmpty(lor.getOwnerId())){
			sql.append(" OWNER_ID = :ownerId,");
		}
		if(!StringUtils.isNullOrEmpty(lor.getTitle())){
			sql.append(" TITLE=:title,");
		}
		if(!StringUtils.isNullOrEmpty(lor.getPics())){
			sql.append(" PICS=:pics,");
		}
		if(!StringUtils.isNullOrEmpty(lor.getOptions())){
			sql.append(" OPTIONS=:options,");
		}
		if(!StringUtils.isNullOrEmpty(lor.getContent())){
			sql.append(" CONTENT=:content,");
		}
		if(lor.getCommentCount()!=0){
			sql.append(" COMMENT_COUNT=:commentCount,");
		}
		String sqlStr = sql.substring(0, sql.length()-1) + " WHERE ID = :id";
		return es.createSqlUpdate(sqlStr)
			.setParameter("ownerId", lor.getOwnerId())
			.setParameter("title", lor.getTitle())
			.setParameter("pics", lor.getPics())
			.setParameter("options", lor.getOptions())
			.setParameter("content", lor.getContent())
			.setParameter("commentCount", lor.getCommentCount())
			.setParameter("id", lor.getId()).execute();
	}
	
	private static ArrayList<LeftOrRight> convert(List<SqlRow> rows) throws Exception{
		ArrayList<LeftOrRight> result = new ArrayList<LeftOrRight>();
		for(SqlRow row : rows){
			result.add(convert(row));
		}
		return result;
	}
	
	private static LeftOrRight convert(SqlRow row) throws Exception{
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		LeftOrRight lr = LeftOrRight.create()
				.setId(row.getString("ID"))
				.setOwnerId(row.getString("OWNER_ID"))
				.setTitle(row.getString("TITLE"))
				.setPics(row.getString("PICS"))
				.setOptions(row.getString("OPTIONS"))
				.setContent(row.getString("CONTENT"))
				.setCommentCount(row.getInteger("COMMENT_COUNT"))
				.setCreateAt(format.format(row.getDate("CREATE_AT")))
				.setNickName(row.getString("NICKNAME"))
				.setHeadImg(row.getString("PORTRAIT_KEY"))
				.setMyResult(row.getInteger("RESULT"));
		return lr;
	}
	
	/****************************************Setters And Getters**********************************************/
	public String getId() {
		return id == null ? "" : id;
	}
	public LeftOrRight setId(String id) {
		this.id = id;
		return this;
	}
	public String getOwnerId() {
		return ownerId == null ? "" : ownerId;
	}
	public LeftOrRight setOwnerId(String ownerId) {
		this.ownerId = ownerId;
		return this;
	}
	public String getTitle() {
		return title == null ? "" : title;
	}
	public LeftOrRight setTitle(String title) {
		this.title = title;
		return this;
	}
	public String getPics() {
		return pics == null ? "" : pics;
	}
	public LeftOrRight setPics(String pics) {
		this.pics = pics;
		return this;
	}
	public String getOptions() {
		return options == null ? "" : options;
	}
	public LeftOrRight setOptions(String options) {
		this.options = options;
		return this;
	}
	public String getContent() {
		return content == null ? "" : content;
	}
	public LeftOrRight setContent(String content) {
		this.content = content;
		return this;
	}
	public Integer getCommentCount() {
		return commentCount == null ? 0 : commentCount;
	}
	public LeftOrRight setCommentCount(Integer commentCount) {
		this.commentCount = commentCount;
		return this;
	}
	public String getCreateAt() {
		return createAt == null ? "" : createAt;
	}
	public LeftOrRight setCreateAt(String createAt) {
		this.createAt = createAt;
		return this;
	}

	public String getNickName() {
		return nickName == null ? "" : nickName;
	}

	public LeftOrRight setNickName(String nickName) {
		this.nickName = nickName;
		return this;
	}

	public String getHeadImg() {
		return headImg == null ? "" : headImg;
	}

	public LeftOrRight setHeadImg(String headImg) {
		this.headImg = headImg;
		return this;
	}

	public Integer getMyResult() {
		return myResult;
	}

	public LeftOrRight setMyResult(Integer myResult) {
		this.myResult = myResult;
		return this;
	}
	
}
